"""PostGIS空间数据仓储层

实现空间数据的查询操作，支持三种典型空间关系演示。
"""

from typing import List, Dict, Any, Optional
from sqlalchemy.orm import Session
from sqlalchemy import text, cast
from geoalchemy2.functions import ST_Contains, ST_Intersects, ST_Within, ST_AsGeoJSON, ST_Area, ST_Centroid, ST_Perimeter, ST_X, ST_Y
from geoalchemy2.types import Geography
import json
import tempfile
import os

from ..models.spatial import SpatialPoint, SpatialPolygon, SpatialRegion
from ..database.connection import DatabaseManager


class SpatialRepository:
    """空间数据仓储类"""
    
    def __init__(self, db_manager: DatabaseManager):
        """初始化空间数据仓储
        
        Args:
            db_manager: 数据库管理器
        """
        self.db_manager = db_manager
    
    def get_points_in_polygon(self, polygon_id: int) -> List[Dict[str, Any]]:
        """查询包含在指定面要素内的所有点要素
        
        Args:
            polygon_id: 面要素ID
            
        Returns:
            包含在面内的点要素列表
        """
        session = self.db_manager.create_session()
        try:
            # 使用PostGIS的ST_Contains函数查询点在面内的关系
            query = session.query(
                SpatialPoint.id,
                SpatialPoint.name,
                SpatialPoint.description,
                SpatialPoint.category,
                ST_AsGeoJSON(SpatialPoint.geometry).label('geometry')
            ).join(
                SpatialPolygon,
                ST_Contains(SpatialPolygon.geometry, SpatialPoint.geometry)
            ).filter(
                SpatialPolygon.id == polygon_id
            ).order_by(SpatialPoint.name)
            
            results = []
            for row in query.all():
                results.append({
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'category': row.category,
                    'geometry': json.loads(row.geometry)
                })
            
            return results
        finally:
            session.close()
    
    def get_polygons_in_region(self, region_id: int) -> List[Dict[str, Any]]:
        """查询包含在指定区域内的所有面要素
        
        Args:
            region_id: 区域ID
            
        Returns:
            包含在区域内的面要素列表
        """
        session = self.db_manager.create_session()
        try:
            # 使用PostGIS的ST_Within函数查询面在区域内的关系
            query = session.query(
                SpatialPolygon.id,
                SpatialPolygon.name,
                SpatialPolygon.description,
                SpatialPolygon.category,
                SpatialPolygon.area,
                ST_AsGeoJSON(SpatialPolygon.geometry).label('geometry')
            ).join(
                SpatialRegion,
                ST_Within(SpatialPolygon.geometry, SpatialRegion.geometry)
            ).filter(
                SpatialRegion.id == region_id
            ).order_by(SpatialPolygon.name)
            
            results = []
            for row in query.all():
                results.append({
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'category': row.category,
                    'area': row.area,
                    'geometry': json.loads(row.geometry)
                })
            
            return results
        finally:
            session.close()
    
    def get_intersecting_polygons(self, polygon_id: int) -> List[Dict[str, Any]]:
        """查询与指定面要素相交的所有面要素
        
        Args:
            polygon_id: 面要素ID
            
        Returns:
            与指定面相交的面要素列表
        """
        session = self.db_manager.create_session()
        try:
            # 使用PostGIS的ST_Intersects函数查询面与面相交的关系
            target_polygon = session.query(SpatialPolygon).filter(
                SpatialPolygon.id == polygon_id
            ).first()
            
            if not target_polygon:
                return []
            
            query = session.query(
                SpatialPolygon.id,
                SpatialPolygon.name,
                SpatialPolygon.description,
                SpatialPolygon.category,
                SpatialPolygon.area,
                ST_AsGeoJSON(SpatialPolygon.geometry).label('geometry')
            ).filter(
                SpatialPolygon.id != polygon_id,
                ST_Intersects(SpatialPolygon.geometry, target_polygon.geometry)
            ).order_by(SpatialPolygon.name)
            
            results = []
            for row in query.all():
                results.append({
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'category': row.category,
                    'area': row.area,
                    'geometry': json.loads(row.geometry)
                })
            
            return results
        finally:
            session.close()
    
    def get_all_points(self) -> List[Dict[str, Any]]:
        """获取所有点要素
        
        Returns:
            所有点要素列表
        """
        session = self.db_manager.create_session()
        try:
            query = session.query(
                SpatialPoint.id,
                SpatialPoint.name,
                SpatialPoint.description,
                SpatialPoint.category,
                ST_AsGeoJSON(SpatialPoint.geometry).label('geometry')
            ).order_by(SpatialPoint.name)
            
            results = []
            for row in query.all():
                results.append({
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'category': row.category,
                    'geometry': json.loads(row.geometry)
                })
            
            return results
        finally:
            session.close()
    
    def get_all_polygons(self) -> List[Dict[str, Any]]:
        """获取所有面要素
        
        Returns:
            所有面要素列表
        """
        session = self.db_manager.create_session()
        try:
            query = session.query(
                SpatialPolygon.id,
                SpatialPolygon.name,
                SpatialPolygon.description,
                SpatialPolygon.category,
                SpatialPolygon.area,
                ST_AsGeoJSON(SpatialPolygon.geometry).label('geometry')
            ).order_by(SpatialPolygon.name)
            
            results = []
            for row in query.all():
                results.append({
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'category': row.category,
                    'area': row.area,
                    'geometry': json.loads(row.geometry)
                })
            
            return results
        finally:
            session.close()
    
    def get_all_regions(self) -> List[Dict[str, Any]]:
        """获取所有区域
        
        Returns:
            所有区域列表
        """
        session = self.db_manager.create_session()
        try:
            query = session.query(
                SpatialRegion.id,
                SpatialRegion.name,
                SpatialRegion.description,
                SpatialRegion.region_type,
                SpatialRegion.level,
                ST_AsGeoJSON(SpatialRegion.geometry).label('geometry')
            ).order_by(SpatialRegion.name)
            
            results = []
            for row in query.all():
                results.append({
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'region_type': row.region_type,
                    'level': row.level,
                    'geometry': json.loads(row.geometry)
                })
            
            return results
        finally:
            session.close()
    
    def get_polygon_by_id(self, polygon_id: int) -> Optional[Dict[str, Any]]:
        """根据ID获取面要素
        
        Args:
            polygon_id: 面要素ID
            
        Returns:
            面要素信息
        """
        session = self.db_manager.create_session()
        try:
            row = session.query(
                SpatialPolygon.id,
                SpatialPolygon.name,
                SpatialPolygon.description,
                SpatialPolygon.category,
                SpatialPolygon.area,
                ST_AsGeoJSON(SpatialPolygon.geometry).label('geometry')
            ).filter(
                SpatialPolygon.id == polygon_id
            ).first()
            
            if row:
                return {
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'category': row.category,
                    'area': row.area,
                    'geometry': json.loads(row.geometry)
                }
            
            return None
        finally:
            session.close()
    
    def export_region_as_geojson(self, region_id: int) -> Optional[str]:
        """导出区域为GeoJSON格式
        
        Args:
            region_id: 区域ID
            
        Returns:
            GeoJSON字符串，如果失败返回None
        """
        session = self.db_manager.create_session()
        try:
            # 获取区域的完整信息和几何数据
            row = session.query(
                SpatialRegion.id,
                SpatialRegion.name,
                SpatialRegion.description,
                SpatialRegion.region_type,
                SpatialRegion.level,
                ST_AsGeoJSON(SpatialRegion.geometry).label('geometry'),
                # 计算面积（平方米）
                ST_Area(cast(SpatialRegion.geometry, Geography)).label('area_sqm'),
                # 计算中心点
                ST_X(ST_Centroid(SpatialRegion.geometry)).label('centroid_lon'),
                ST_Y(ST_Centroid(SpatialRegion.geometry)).label('centroid_lat'),
                # 计算周长（米）
                ST_Perimeter(cast(SpatialRegion.geometry, Geography)).label('perimeter_m')
            ).filter(
                SpatialRegion.id == region_id
            ).first()
            
            if not row:
                return None
            
            # 构建GeoJSON Feature
            geometry = json.loads(row.geometry) if row.geometry else None
            
            # 转换面积单位
            area_sqm = float(row.area_sqm) if row.area_sqm else 0
            area_sqkm = area_sqm / 1_000_000
            
            # 转换周长单位
            perimeter_m = float(row.perimeter_m) if row.perimeter_m else 0
            perimeter_km = perimeter_m / 1000
            
            feature = {
                "type": "Feature",
                "properties": {
                    "id": row.id,
                    "name": row.name,
                    "description": row.description or "",
                    "region_type": row.region_type or "",
                    "level": row.level or 0,
                    "area_sqm": area_sqm,
                    "area_sqkm": area_sqkm,
                    "perimeter_m": perimeter_m,
                    "perimeter_km": perimeter_km,
                    "centroid_lon": float(row.centroid_lon) if row.centroid_lon else None,
                    "centroid_lat": float(row.centroid_lat) if row.centroid_lat else None
                },
                "geometry": geometry
            }
            
            # 构建完整的GeoJSON FeatureCollection
            geojson = {
                "type": "FeatureCollection",
                "features": [feature]
            }
            
            return json.dumps(geojson, ensure_ascii=False, indent=2)
            
        except Exception as e:
            print(f"导出GeoJSON时发生错误: {e}")
            return None
        finally:
            session.close()
    
    def create_shapefile_from_region(self, region_id: int) -> Optional[str]:
        """为区域创建Shapefile文件
        
        Args:
            region_id: 区域ID
            
        Returns:
            临时文件路径，如果失败返回None
        """
        try:
            # 首先尝试导入fiona和shapely
            try:
                import fiona
                from shapely.geometry import shape
                from fiona.crs import from_epsg
            except ImportError:
                print("需要安装fiona和shapely库来支持Shapefile导出")
                return None
            
            # 获取GeoJSON数据
            geojson_str = self.export_region_as_geojson(region_id)
            if not geojson_str:
                return None
            
            geojson_data = json.loads(geojson_str)
            feature = geojson_data['features'][0]
            
            # 创建临时文件
            temp_dir = tempfile.mkdtemp()
            shapefile_path = os.path.join(temp_dir, f"region_{region_id}.shp")
            
            # 定义schema
            schema = {
                'geometry': 'Polygon',
                'properties': {
                    'id': 'int',
                    'name': 'str:100',
                    'descriptio': 'str:255',  # 字段名限制为10个字符
                    'region_typ': 'str:50',
                    'level': 'int',
                    'area_sqm': 'float',
                    'area_sqkm': 'float',
                    'perim_m': 'float',
                    'perim_km': 'float',
                    'cent_lon': 'float',
                    'cent_lat': 'float'
                }
            }
            
            # 写入Shapefile
            with fiona.open(
                shapefile_path, 
                'w', 
                driver='ESRI Shapefile',
                crs=from_epsg(4326),
                schema=schema
            ) as output:
                # 转换属性名以符合Shapefile限制
                properties = {
                    'id': feature['properties']['id'],
                    'name': feature['properties']['name'][:100],
                    'descriptio': feature['properties']['description'][:255],
                    'region_typ': feature['properties']['region_type'][:50],
                    'level': feature['properties']['level'],
                    'area_sqm': feature['properties']['area_sqm'],
                    'area_sqkm': feature['properties']['area_sqkm'],
                    'perim_m': feature['properties']['perimeter_m'],
                    'perim_km': feature['properties']['perimeter_km'],
                    'cent_lon': feature['properties']['centroid_lon'],
                    'cent_lat': feature['properties']['centroid_lat']
                }
                
                output.write({
                    'geometry': feature['geometry'],
                    'properties': properties
                })
            
            return shapefile_path
            
        except Exception as e:
            print(f"创建Shapefile时发生错误: {e}")
            return None
    
    def get_region_gis_analysis(self, region_id: int) -> Optional[Dict[str, Any]]:
        """获取区域的详细GIS分析信息
        
        Args:
            region_id: 区域ID
            
        Returns:
            区域的GIS分析信息，包括面积、中心点、周长等
        """
        session = self.db_manager.create_session()
        try:
            # 使用PostGIS函数计算各种GIS指标
            row = session.query(
                SpatialRegion.id,
                SpatialRegion.name,
                SpatialRegion.description,
                SpatialRegion.region_type,
                SpatialRegion.level,
                ST_AsGeoJSON(SpatialRegion.geometry).label('geometry'),
                # 计算面积（平方米）
                ST_Area(cast(SpatialRegion.geometry, Geography)).label('area_sqm'),
                # 计算中心点
                ST_X(ST_Centroid(SpatialRegion.geometry)).label('centroid_lon'),
                ST_Y(ST_Centroid(SpatialRegion.geometry)).label('centroid_lat'),
                # 计算周长（米）
                ST_Perimeter(cast(SpatialRegion.geometry, Geography)).label('perimeter_m')
            ).filter(
                SpatialRegion.id == region_id
            ).first()
            
            if row:
                # 转换面积单位
                area_sqm = float(row.area_sqm) if row.area_sqm else 0
                area_sqkm = area_sqm / 1_000_000  # 转换为平方公里
                
                # 转换周长单位
                perimeter_m = float(row.perimeter_m) if row.perimeter_m else 0
                perimeter_km = perimeter_m / 1000  # 转换为公里
                
                return {
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'region_type': row.region_type,
                    'level': row.level,
                    'geometry': json.loads(row.geometry),
                    'gis_metrics': {
                        'area_sqm': round(area_sqm, 2),
                        'area_sqkm': round(area_sqkm, 2),
                        'centroid_lat': round(float(row.centroid_lat), 6) if row.centroid_lat else None,
                        'centroid_lon': round(float(row.centroid_lon), 6) if row.centroid_lon else None,
                        'perimeter_m': round(perimeter_m, 2),
                        'perimeter_km': round(perimeter_km, 2)
                    }
                }
            
            return None
        finally:
            session.close()
    
    def get_educational_institutions_by_regions(self) -> Dict[str, List[Dict[str, Any]]]:
        """获取与各个spatial_regions相交的教育机构
        
        Returns:
            按区域分组的教育机构列表
        """
        session = self.db_manager.create_session()
        try:
            # 获取所有区域
            regions = session.query(SpatialRegion).all()
            
            result = {}
            for region in regions:
                # 查询与该区域相交的教育机构（点）
                educational_points = session.query(
                    SpatialPoint.id,
                    SpatialPoint.name,
                    SpatialPoint.description,
                    SpatialPoint.category,
                    ST_AsGeoJSON(SpatialPoint.geometry).label('geometry')
                ).filter(
                    ST_Intersects(SpatialPoint.geometry, region.geometry),
                    SpatialPoint.category.like('%教育%')
                ).all()
                
                institutions = []
                for point in educational_points:
                    institutions.append({
                        'id': point.id,
                        'name': point.name,
                        'description': point.description,
                        'category': point.category,
                        'geometry': json.loads(point.geometry)
                    })
                
                if institutions:  # 只包含有教育机构的区域
                    result[region.name] = {
                        'region_info': {
                            'id': region.id,
                            'name': region.name,
                            'description': region.description,
                            'region_type': region.region_type,
                            'level': region.level
                        },
                        'institutions': institutions
                    }
            
            return result
        finally:
            session.close()
    
    def get_polygons_containing_point(self, point_id: int) -> List[Dict[str, Any]]:
        """获取包含指定点的所有面要素
        
        Args:
            point_id: 点ID
            
        Returns:
            包含该点的所有面要素列表
        """
        session = self.db_manager.create_session()
        try:
            query = session.query(
                SpatialPolygon.id,
                SpatialPolygon.name,
                SpatialPolygon.description,
                SpatialPolygon.category,
                SpatialPolygon.area,
                ST_AsGeoJSON(SpatialPolygon.geometry).label('geometry')
            ).join(
                SpatialPoint,
                ST_Contains(SpatialPolygon.geometry, SpatialPoint.geometry)
            ).filter(
                SpatialPoint.id == point_id
            ).order_by(SpatialPolygon.name)
            
            results = []
            for row in query.all():
                results.append({
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'category': row.category,
                    'area': row.area,
                    'geometry': json.loads(row.geometry)
                })
            
            return results
        finally:
            session.close()
    
    def get_intersecting_polygons(self, polygon_id: int) -> List[Dict[str, Any]]:
        """获取与指定面相交的所有面要素
        
        Args:
            polygon_id: 面ID
            
        Returns:
            与该面相交的所有面要素列表（不包括自身）
        """
        session = self.db_manager.create_session()
        try:
            # 首先获取目标面的几何信息
            target_polygon = session.query(SpatialPolygon.geometry).filter(
                SpatialPolygon.id == polygon_id
            ).first()
            
            if not target_polygon:
                return []
            
            # 查询与目标面相交的所有面
            query = session.query(
                SpatialPolygon.id,
                SpatialPolygon.name,
                SpatialPolygon.description,
                SpatialPolygon.category,
                SpatialPolygon.area,
                ST_AsGeoJSON(SpatialPolygon.geometry).label('geometry')
            ).filter(
                ST_Intersects(SpatialPolygon.geometry, target_polygon.geometry),
                SpatialPolygon.id != polygon_id  # 排除自身
            ).order_by(SpatialPolygon.name)
            
            results = []
            for row in query.all():
                results.append({
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'category': row.category,
                    'area': row.area,
                    'geometry': json.loads(row.geometry)
                })
            
            return results
        finally:
            session.close()
    
    def get_region_by_id(self, region_id: int) -> Optional[Dict[str, Any]]:
        """根据ID获取区域
        
        Args:
            region_id: 区域ID
            
        Returns:
            区域信息
        """
        session = self.db_manager.create_session()
        try:
            row = session.query(
                SpatialRegion.id,
                SpatialRegion.name,
                SpatialRegion.description,
                SpatialRegion.region_type,
                SpatialRegion.level,
                ST_AsGeoJSON(SpatialRegion.geometry).label('geometry')
            ).filter(
                SpatialRegion.id == region_id
            ).first()
            
            if row:
                return {
                    'id': row.id,
                    'name': row.name,
                    'description': row.description,
                    'region_type': row.region_type,
                    'level': row.level,
                    'geometry': json.loads(row.geometry)
                }
            
            return None
        finally:
            session.close()